#!/bin/bash
db_host="127.0.0.1"
db_port=3306
db_user="root"
db_password="123456"

doSql () {
    mysql -h${db_host} -P${db_port} -u${db_user} -p"${db_password}" -e "$*"
}

i=0
db="db_zwx2018"
while (($i <= 99))
do
    hashStr=`printf "%02d" ${i}`
    echo "create table t_article_${hashStr}"
    sql="create table ${db}.t_article_${hashStr} (
           \`uid\` int unsigned not null comment '作者id',
           \`article_id\` int unsigned not null comment '文章id',
           \`title\` varchar(150) not null comment '标题',
           \`content\` text comment '正文',
           \`keyword\` varchar(150) not null default '' comment '关键词',
           \`category_id\` int unsigned not null default 0 comment '分类id',
           \`status\` tinyint unsigned not null default 0 comment '1:正常，2:屏蔽',
           \`create_time\` int unsigned not null default 0 comment '创建时间',
           \`update_time\` int unsigned not null default 0 comment '更新时间',
           primary key (\`article_id\`),
           index \`idx_uid\` (\`uid\`),
           index \`idx_category\` (\`category_id\`),
           index \`idx_create_time\` (\`create_time\`),
           FULLTEXT (\`title\`, \`keyword\`, \`content\`)
         ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='文章';"
    doSql $sql

    echo "create table t_article_stat_${hashStr}"
    sql="create table ${db}.t_article_stat_${hashStr} (
           \`article_id\` int unsigned not null comment '文章id',
           \`like_count\` int unsigned not null default 0 comment '点赞数',
           \`view_count\` int unsigned not null default 0 comment '浏览数',
           \`comment_count\` int unsigned not null default 0 comment '评论数',
           primary key (\`article_id\`)
         ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='文章统计';"
    doSql $sql

    echo "create table t_article_comment_${hashStr}"
    sql="create table ${db}.t_article_comment_${hashStr} (
           \`comment_id\` int unsigned not null auto_increment comment '评论id',
           \`uid\` int unsigned not null comment '评论者id',
           \`article_id\` int unsigned not null comment '文章id',
           \`content\` varchar(255) not null comment '评论内容,base64编码',
           \`status\` tinyint unsigned not null default 0 comment '1:正常，2屏蔽',
           \`create_time\`  int unsigned not null default 0 comment '创建时间',
           \`pid\`  int unsigned not null default 0 comment '父评论id',
           primary key (\`comment_id\`),
           index \`idx_uid\` (\`uid\`),
           index \`idx_article\` (\`article_id\`),
           index \`idx_pid\` (\`pid\`),
           index \`idx_create_time\` (\`create_time\`)
         ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='文章评论';"
    doSql $sql
    let i++
done

echo "create table t_category"
sql="create table ${db}.t_category (
       \`category_id\` int unsigned not null primary key auto_increment,
       \`name\` varchar(20) not null
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='分类';"
doSql $sql
